<?php

include("connect.php");
include("header.php");







function getDateOfRecord($value) {


$blah = substr($value, 4, 4);     // bcdef
$blah =  $blah."-".substr($value, 2, 2);
$blah = $blah."-".substr($value, 0, 2);
  
return $blah;


}

function getPublicationName($pdfname, $dblink) {


$query = sprintf("SELECT `Name` FROM Publication WHERE `Name` = '%s'", mysql_real_escape_string($pdfname));

	 $result = mysql_query($query, $dblink);



	

	// Check result
	// This shows the actual query sent to MySQL, and the error. Useful for debugging.
	if (!$result) {
		$message  = 'Invalid query: ' . mysql_error() . "\n";
	    	$message .= 'Whole query: ' . $query;
    		die($message);
	}
$value = "";
if(mysql_num_rows($result)!=0) {
		$value = mysql_result($result, 0); // outputs third employee's name

}
	return $value;
	

}

function insertNewPublication($country, $datepublished, $pdfname, $noticetype, $dblink) {
	


	$blah = getPublicationName($pdfname, $dblink);

	if($blah != "" && $blah != null) {
		
		return $blah;
	} else {

	$query = sprintf("UPDATE Publication SET `Name` = '%s', `NotificationID` = '%s', `DatePublished` = '%s', `CountryID` = '%s', `AddedOn` = '%s' where `ID` = '%s'", 
				mysql_real_escape_string($pdfname),
				mysql_real_escape_string($noticetype), 
				mysql_real_escape_string($datepublished), 
				mysql_real_escape_string($country),
        gmdate("Y-m-j H:i:s", time() + 3600*12),
	mysql_real_escape_string($_GET["publication"]));
echo $query;
	 $result = mysql_query($query, $dblink);

	// Check result
	// This shows the actual query sent to MySQL, and the error. Useful for debugging.
	if (!$result) {
		$message  = 'Invalid query: ' . mysql_error() . "\n";
	    	$message .= 'Whole query: ' . $query;
    		die($message);
	}

	return  mysql_insert_id();
}
}








if($_POST["name"] != NULL && $_POST["name"] != "" && $_GET["publication"] != NULL && $_GET["publication"] != "") {

$notetype2 = $_POST["noticetype"];
$notetype = "";

if($notetype2 == "1") {
	$notetype = "DEATH-NOTICE";
} else if ($notetype2 == "2") {
	$notetype = "BIRTH-NOTICE";
} else if ($notetype2 == "3") {
	$notetype = "WEDDING-NOTICE";
}


$pdfname = $notetype."-".$_POST["name"]."-".$_POST["date"];


echo $pdfname;

echo $notetype;

echo $_POST["name"];

echo $_POST["date"];

$publicationid = insertNewPublication($_POST["country"], getDateOfRecord($_POST["date"]), $pdfname, $_POST["noticetype"], $link);

echo "Record Updated";

}













function FormatDate($value) {
	$year = substr($value, 0, 4);     // bcdef
$month =  substr($value, 5, 2);
$day = substr($value, 8, 2);

return $day.$month.$year;

}

$query = "select ID, Name, NotificationID, DatePublished, CountryID from Publication where id = ".mysql_real_escape_string($_GET['publication']);

// Perform Query
$result = mysql_query($query, $link);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

$ID = "";
$Name = "";
$NotificationID = "";
$DatePublished = "";
$CountryID = "";

while ($row = mysql_fetch_assoc($result)) {
	$ID = $row['ID'];
	$Names = explode("-", $row['Name']);
	$Name = $Names[2];
	$NotificationID = $row['NotificationID'];
	$DatePublished = $row['DatePublished'];
	$CountryID = $row['CountryID'];
}

mysql_free_result($result);



echo "<form action=\"editform.php?publication=".$ID."\" method=\"POST\"><p>Please complete the form below.</p><fieldset class=\"login\"><legend>Publication Details</legend>";

echo "<input type=\"hidden\" name=\"ID\" value=\"".$ID."\" />";





$query = "Select `ID`, `Name` from Country";

// Perform Query
$result = mysql_query($query, $link);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

echo "<div><label for=\"country\">Country</label><select id=\"country\" name=\"country\">\n";
while ($row = mysql_fetch_assoc($result)) {
	

if($row['ID'] == $CountryID) {
	echo "<option value=\"".$row['ID']."\" selected=\"selected\">".$row['Name']."</option>\n";
} else {
	echo "<option value=\"".$row['ID']."\">".$row['Name']."</option>\n";
}
	
}
echo "</select></div>";


mysql_free_result($result);






$query = "Select `ID`, `Name` from Notification";

// Perform Query
$result = mysql_query($query, $link);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

echo "<div><label for=\"noticetype\">Notice Type</label><select name=\"noticetype\">\n";
while ($row = mysql_fetch_assoc($result)) {
	
if($row['ID'] == $NotificationID) {
	echo "<option value=\"".$row['ID']."\" selected=\"selected\">".$row['Name']."</option>\n";
} else {
	echo "<option value=\"".$row['ID']."\">".$row['Name']."</option>\n";
}
	
}
echo "</select></div>";


mysql_free_result($result);


$query = "Select `ID`, `ShortName` from Newspapers";

// Perform Query
$result = mysql_query($query, $link);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

echo "<div><label for=\"name\">Publication Name</label><select name=\"name\">\n";
while ($row = mysql_fetch_assoc($result)) {
	

if($row['ShortName'] == $Name) {
	echo "<option value=\"".$row['ShortName']."\" selected=\"selected\">".$row['ShortName']."</option>\n";
} else {
	echo "<option value=\"".$row['ShortName']."\">".$row['ShortName']."</option>\n";
}

	
}
echo "</select></div>";
$date = FormatDate($DatePublished); 
echo "<div><label for=\"date\">Date Published</label><input type=\"text\" name=\"date\" id=\"date\" value=\"$date\" /></div></fieldset>

 

<div><input type=\"submit\" id=\"submit\" name=\"submit\" value=\"Submit\" /></div>
</form>";


mysql_free_result($result);






$query = "select 	Person.ID, 
	Person.Surname, 
	Person.FirstNames, 
	Person.FormerName, 
	Person.NeeName, 
	Person.AlsoKnownAs, 
	Person.DeathDate, 
	Person.BirthDate, 
	Person.Age AS 'Age', 
	Person.AgeMeasure AS 'AgeMeasure', 
	CityTown.Name as 'CityTown', 
	c2.Name as 'FormerCityTown', 
	Region.Name AS 'Region', 
	Country.Name AS 'Country', 
	Charity.Name AS 'Charity', 
	FuneralHome.Name AS 'FuneralHome',
  Person.OtherInfo As 'OtherInformation',
Person.AddedOn AS 'Modified'

from Person 
left join Region on Person.RegionID = Region.ID 
left join Country on Country.ID = Person.CountryID 
left join CityTown AS c2 on Person.FormerCityTownID = c2.ID 
left join Charity on Charity.ID = Person.CharityID 
left join FuneralHome on FuneralHome.ID = Person.FuneralHomeID 
left join CityTown on CityTown.ID = Person.CityTownID
left join PublicationPerson on Person.ID = PublicationPerson.PersonID left join Publication on Publication.ID = PublicationPerson.PublicationID where Publication.ID =".mysql_real_escape_string($_GET['publication']);


// Perform Query
$result = mysql_query($query, $link);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

$class1 = "odd";
$class2 = "even";
$class = $class1;

echo "<div class=\"classicborder\"><table><tr><th>Remove</th><th>ID</th><th>Surname</th><th>First Names</th><th>Former Name</th><th>Nee Name</th><th>AKA</th><th>Death Date</th><th>Birth Date</th><th>Age</th><th>City/Town</th><th>Former City/Town</th><th>Region</th><th>Country</th><th>Charity</th><th>Funeral Home</th><th>Other Info</th><th>Added On</th></tr>\n";


while ($row = mysql_fetch_assoc($result)) {
echo "<tr class=\"$class\">";
echo "<td><a href=\"unlinkpersonpublication.php?PersonID=".$row['ID']."&PubID=".$ID."\">".Remove."</a></td>\n";
echo "<td><a href=\"associated.php?ID=".$row['ID']."\">".$row['ID']."</a></td>\n";
echo "<td>".str_replace("&", "&amp;", $row['Surname'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['FirstNames'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['FormerName'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['NeeName'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['AlsoKnownAs'])."</td>";
echo "<td>".date('d-m-Y', strtotime(str_replace("&", "&amp;", $row['DeathDate'])))."</td>";
echo "<td>".date('d-m-Y', strtotime(str_replace("&", "&amp;", $row['BirthDate'])))."</td>";
echo "<td>".str_replace("&", "&amp;", $row['Age']).str_replace("&", "&amp;", $row['AgeMeasure'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['CityTown'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['FormerCityTown'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['Region'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['Country'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['Charity'])."</td>";
echo "<td>".str_replace("&", "&amp;", $row['FuneralHome'])."</td>\n";
echo "<td>".str_replace("&", "&amp;", $row['OtherInformation'])."</td>\n";
echo "<td>".str_replace("&", "&amp;", $row['AddedOn'])."</td>\n</tr>\n";

if($class == $class1) {
	$class = $class2;
} else if ($class == $class2) {
	$class = $class1;
}

}
echo "</table></div></body></html>";









// Free the resources associated with the result set
// This is done automatically at the end of the script

mysql_close($link);
?>
